﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;

namespace Chenxin.Lib.Hr.DAL
{
    public class WorkReport
    {
        static public DataSet getOgrMembersByParentId(Guid ParentId, Guid GrandfatherId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT (UserName + '(' + UserCode + ')')  AS NodeName,UserId AS NodeId, 1 AS IsLeaf ");
            strSql.Append("FROM Hr_UserInfo ");
            strSql.Append("WHERE BelongDepartId = @ParentId ");
            strSql.Append("And [Status] = 1 ");
            //strSql.Append("And IsVirtual = 0 ");
            strSql.Append("And Validate = 1 ");
            strSql.Append("UNION ALL ");
            strSql.Append("SELECT DepartName AS NodeName,ChildId AS NodeId, 0 AS IsLeaf ");
            strSql.Append("FROM Hr_Struct_DepartTreeRelation left join Hr_DepartmentInfo ON (ChildId = DepartId) ");
            if (GrandfatherId.Equals(Guid.Empty))
            {
                strSql.Append("WHERE ParentId = @ParentId ");
            }
            else
            {
                if (GrandfatherId.Equals(new Guid("00000000-0000-0000-0000-000000000001")))
                {
                    GrandfatherId = new Guid("00000000-0000-0000-0000-000000000000");
                }
                strSql.Append("WHERE ParentId = @GrandfatherId ");
                strSql.Append("And ChildId = @ParentId ");
            }
            strSql.Append("And IsOpen = 1 ");
            strSql.Append("Order By NodeName ");

            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ParentId", DbType.Guid, ParentId);
            db.AddInParameter(dbCommand, "GrandfatherId", DbType.Guid, GrandfatherId);

            return db.ExecuteDataSet(dbCommand);
        }

        static public DataSet getRoleMembersByParentId(Guid ParentId, Guid GrandfatherId)
        {
            StringBuilder strSql = new StringBuilder();
            if (GrandfatherId.Equals(Guid.Empty))
            {
                strSql.Append("SELECT DepartName AS NodeName,ChildId AS NodeId, 0 AS IsLeaf ");
                strSql.Append("FROM Hr_Struct_DepartTreeRelation left join Hr_DepartmentInfo ON (ChildId = DepartId) ");
                strSql.Append("WHERE DepartProperty < 300 ");
                strSql.Append("AND ParentId = @ParentId ");
                strSql.Append("UNION ALL ");
                strSql.Append("SELECT RoleName AS NodeName,RoleId AS NodeId, 0 AS IsLeaf ");
                strSql.Append("FROM Base_Permit_RoleDefine ");
                strSql.Append("WHERE CompanyId = @ParentId ");
                strSql.Append("And IsShow = 1 ");
                strSql.Append("UNION ALL ");
                strSql.Append("SELECT  ");
                strSql.Append("((");
		        strSql.Append("SELECT Hr_DepartmentInfo.DepartName "); 
		        strSql.Append("FROM Hr_Struct_DepartTreeRelation LEFT JOIN Hr_DepartmentInfo ");
		        strSql.Append("ON(Hr_Struct_DepartTreeRelation.ParentId = Hr_DepartmentInfo.DepartId) ");
		        strSql.Append("WHERE ChildId = Hr_UserInfo.DepartId");
		        strSql.Append(")");
                strSql.Append(" + '/' + DepartName + '/' + UserName) AS NodeName,");
                strSql.Append("Hr_UserInfo.UserId AS NodeId, 1 AS IsLeaf ");
                strSql.Append("FROM Base_RoleUserRelationInfo LEFT JOIN Hr_UserInfo  ");
                strSql.Append("ON (Base_RoleUserRelationInfo.UserId = Hr_UserInfo.UserId) ");
                strSql.Append("WHERE RoleId = @ParentId ");
                strSql.Append("And [Status] = 1 ");
                //strSql.Append("And IsVirtual = 0 ");
                strSql.Append("And Validate = 1 ");
                strSql.Append("ORDER BY NodeName ");
            }
            else
            {
                strSql.Append("SELECT DepartName AS NodeName,ChildId AS NodeId, 0 AS IsLeaf ");
                strSql.Append("FROM Hr_Struct_DepartTreeRelation left join Hr_DepartmentInfo ON (ChildId = DepartId) ");
                strSql.Append("WHERE DepartProperty < 300 ");
                if (GrandfatherId.Equals(new Guid("00000000-0000-0000-0000-000000000001")))
                {
                    GrandfatherId = new Guid("00000000-0000-0000-0000-000000000000");
                }
                strSql.Append("And ParentId = @GrandfatherId ");
                strSql.Append("And ChildId = @ParentId ");
                strSql.Append("UNION ALL ");
                strSql.Append("SELECT RoleName AS NodeName,RoleId AS NodeId, 0 AS IsLeaf ");
                strSql.Append("FROM Base_Permit_RoleDefine ");
                strSql.Append("WHERE 1=0 ");
                strSql.Append("And IsShow = 1 ");
                strSql.Append("UNION ALL ");
                strSql.Append("SELECT  UserName AS NodeName,Hr_UserInfo.UserId AS NodeId, 1 AS IsLeaf ");
                strSql.Append("FROM Base_RoleUserRelationInfo LEFT JOIN Hr_UserInfo  ");
                strSql.Append("ON (Base_RoleUserRelationInfo.UserId = Hr_UserInfo.UserId) ");
                strSql.Append("WHERE 1=0 ");
                strSql.Append("ORDER BY NodeName ");
            }
            

            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ParentId", DbType.Guid, ParentId);
            db.AddInParameter(dbCommand, "GrandfatherId", DbType.Guid, GrandfatherId);

            return db.ExecuteDataSet(dbCommand);
        }

        static public DataSet getReceiversInfo(string Receivers)
        {
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main_Search");
            DbCommand dbCommand = db.GetStoredProcCommand("P_GetReceiversInfo");
            db.AddInParameter(dbCommand, "Receivers", DbType.AnsiString, Receivers);
            return db.ExecuteDataSet(dbCommand);
        }

        /// <summary>
        /// 获取指定人当日的前几条工作报告
        /// </summary>
        /// <param name="receivedId"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public static DataSet GetCurrentReport(Guid receivedId, int count)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendFormat("select top {0} b.RecordId,b.Subject,b.SendTime from ", count);
            strSql.Append("dbo.WorkReport_InBox as a join ");
            strSql.Append("dbo.WorkReport_OutBox as b on a.WorkReportId=b.RecordId");

            strSql.Append(" where SendTime>@Date");
            strSql.Append(" and a.ReceiverId=@ReceiverId");
            strSql.Append(" order by SendTime desc");
            Database db = DatabaseFactory.CreateDatabase("Chenxin_Main");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "Date", DbType.String, DateTime.Now.ToString("yyyy-MM-dd"));
            db.AddInParameter(dbCommand, "ReceiverId", DbType.Guid, receivedId);

            return db.ExecuteDataSet(dbCommand);
        }
    }
}
